--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Model Version 0.0.0.0

--This SQL is generated for the table 'EMPLOYEE_TERRITORY'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryDelete]
(
	@EMPLOYEE_user_id [Int] = default,--Entity Framework Required Parent Keys be passed in: Table 'EMPLOYEE'
	@TERRITORY_territory_id [VarChar] (20) = default,--Entity Framework Required Parent Keys be passed in: Table 'TERRITORY'
	@Original_territory_id [VarChar] (20),
	@Original_user_id [Int]
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[EMPLOYEE_TERRITORY] 
WHERE 
	[territory_id] = @Original_territory_id AND
	[user_id] = @Original_user_id ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByEmployeeTerritoryPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritoryPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritoryPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]
FROM 
[EMPLOYEE_TERRITORY]
WHERE

	[EMPLOYEE_TERRITORY].[territory_id] IN (SELECT [territory_id] 
											FROM OpenXML(@hDoc, '//Item', 2) 
											WITH ([territory_id] [Char] (36) 'territory_id')) 

	AND

	[EMPLOYEE_TERRITORY].[user_id] IN (SELECT [user_id] 
											FROM OpenXML(@hDoc, '//Item', 2) 
											WITH ([user_id] [Char] (36) 'user_id')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByEmployeeTerritorySinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritorySinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByEmployeeTerritorySinglePk]
(
	@territory_id [VarChar] (20),
	@user_id [Int]
)
AS

SELECT 
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]

FROM
[EMPLOYEE_TERRITORY]
WHERE 
[EMPLOYEE_TERRITORY].[territory_id] = @territory_id AND [EMPLOYEE_TERRITORY].[user_id] = @user_id 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryInsert]
(
	@territory_id [VarChar] (20) = default,
	@user_id [Int] = default

)
AS
SET NOCOUNT OFF;

DECLARE @created_date [DateTime]
SET @created_date = GetDate()
INSERT INTO [EMPLOYEE_TERRITORY]
(
	[territory_id],
	[user_id]
)
VALUES
(
	@territory_id,
	@user_id
);


SELECT 
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]

FROM
[EMPLOYEE_TERRITORY]
WHERE
	[EMPLOYEE_TERRITORY].[territory_id] = @territory_id AND
	[EMPLOYEE_TERRITORY].[user_id] = @user_id;
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritoryPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritoryPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritoryPagingSelect]
(
	@page [Int], -- page number selected by the user
	@pageSize [Int], -- number of items on the page
	@orderByColumn [Varchar] (100), -- name of column to order things by
	@ascending [Bit], -- order column ascending or descending
	@filter [Varchar] (100) = null, -- filter statement passed in to determine like criteria on order by column
	@count [Int] out -- number of items in the collection
)
AS

SET NOCOUNT ON;

CREATE TABLE #tmpTable
(
	[territory_id] [VarChar] (20),
	[user_id] [Int]
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[territory_id],
	[user_id]
)
SELECT
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]
FROM
[EMPLOYEE_TERRITORY]
WHERE
	(@orderByColumn = 'territory_id' and (((@filter is null) or ([EMPLOYEE_TERRITORY].[territory_id] is null)) or (@filter is not null and [EMPLOYEE_TERRITORY].[territory_id] LIKE @filter)))
or
	(@orderByColumn = 'user_id' and (((@filter is null) or ([EMPLOYEE_TERRITORY].[user_id] is null)) or (@filter is not null and [EMPLOYEE_TERRITORY].[user_id] = @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'territory_id' THEN [EMPLOYEE_TERRITORY].[territory_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'territory_id' THEN [EMPLOYEE_TERRITORY].[territory_id] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE_TERRITORY].[user_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE_TERRITORY].[user_id] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[EMPLOYEE_TERRITORY]
WHERE
	(@orderByColumn = 'territory_id' and (((@filter is null) or ([EMPLOYEE_TERRITORY].[territory_id] is null)) or (@filter is not null and [EMPLOYEE_TERRITORY].[territory_id] LIKE @filter)))
or
	(@orderByColumn = 'user_id' and (((@filter is null) or ([EMPLOYEE_TERRITORY].[user_id] is null)) or (@filter is not null and [EMPLOYEE_TERRITORY].[user_id] = @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]
FROM
	[#tmpTable]
	INNER JOIN [EMPLOYEE_TERRITORY] ON #tmpTable.[territory_id] = [EMPLOYEE_TERRITORY].[territory_id] AND
#tmpTable.[user_id] = [EMPLOYEE_TERRITORY].[user_id]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'territory_id' THEN [EMPLOYEE_TERRITORY].[territory_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'territory_id' THEN [EMPLOYEE_TERRITORY].[territory_id] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE_TERRITORY].[user_id] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'user_id' THEN [EMPLOYEE_TERRITORY].[user_id] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelect]
AS

SET NOCOUNT ON;

SELECT
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]
FROM 
[EMPLOYEE_TERRITORY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByEmployeePks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]
FROM 
[EMPLOYEE_TERRITORY]
WHERE
	[EMPLOYEE_TERRITORY].[user_id] IN (SELECT [user_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([user_id][Int] 'user_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeTerritorySelectByTerritoryPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeTerritorySelectByTerritoryPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_EmployeeTerritorySelectByTerritoryPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
	[EMPLOYEE_TERRITORY].[territory_id],
	[EMPLOYEE_TERRITORY].[user_id]
FROM 
[EMPLOYEE_TERRITORY]
WHERE
	[EMPLOYEE_TERRITORY].[territory_id] IN (SELECT [territory_id]
				FROM OpenXML(@hDoc, '//Item', 2)
				WITH ([territory_id][VarChar] (20) 'territory_id'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

